home *** CD-ROM | disk | FTP | other *** search
- <!DOCTYPE HTML PUBLIC "-//IETF//DTD HTML//EN">
- <html>
-
- <head>
- <meta http-equiv="Content-Type"
- content="text/html; charset=iso-8859-1">
- <meta name="GENERATOR" content="Microsoft FrontPage 2.0">
- <title>Using the Code Module</title>
- </head>
-
- <body bgcolor="#A3CDDA">
- <div align="center"><center>
-
- <table border="1" cellpadding="8" cellspacing="0" width="100%"
- bgcolor="#5A8BAB">
- <tr>
- <td align="center" width="100%"><font color="#FFFF00"
- size="6" face="Arial"><em><strong><b>Urthman's MDB
- Construction Kit.</b></strong></em></font></td>
- </tr>
- </table>
- </center></div><div align="center"><center>
-
- <table border="0" cellpadding="4" cellspacing="4" width="100%">
- <tr>
- <td align="center"><font size="5" face="Arial"><em><b>Using
- the Code Module.</b></em></font></td>
- </tr>
- </table>
- </center></div>
-
- <p align="center"><font size="3" face="Arial"><strong>Once you
- can identify the code module,<br>
- what can you do with it?</strong></font></p>
-
- <hr>
- <div align="center"><center>
-
- <table border="0" cellpadding="8" width="75%">
- <tr>
- <td align="center" width="50%"><a href="#Naming"><font
- face="Arial"><strong>Naming Conventions</strong></font></a></td>
- <td align="center" width="50%"><a href="#ImpHeader"><font
- face="Arial"><strong>Importing with Headers</strong></font></a></td>
- </tr>
- <tr>
- <td align="center" width="50%"><a href="#Exporting"><font
- face="Arial"><strong>Exporting to ASCII files</strong></font></a></td>
- <td align="center" width="50%"><a href="#ImpMods"><font
- face="Arial"><strong>Modify the Import routines</strong></font></a></td>
- </tr>
- <tr>
- <td align="center" width="50%"><a href="#Importing"><font
- face="Arial"><strong>Importing from ASCII files</strong></font></a></td>
- <td align="center" width="50%"> </td>
- </tr>
- <tr>
- <td align="center" width="50%"><a href="#ExpHeader"><font
- face="Arial"><strong>Exporting with Headers</strong></font></a></td>
- <td align="center" width="50%"><a
- href="Urthman's%20MDB%20Construction%20Kit.htm"><font
- face="Arial"><strong>Main Document</strong></font></a></td>
- </tr>
- </table>
- </center></div>
-
- <hr>
-
- <p><a name="Naming"><font face="Arial"><strong>Naming</strong></font></a><font
- face="Arial"><strong> Conventions:</strong></font></p>
-
- <p><font size="2" face="Arial">The code module is generated with
- the assumption that the <strong>Urthman's MDB Code Generator</strong>
- has been (or will be) used to generate the main code module(s)
- for the database itself. All references to data variables revolve
- around this assumption, using the naming convention standardized
- by that code generator.</font></p>
-
- <p><font size="2" face="Arial"><strong><u>Urthman's MDB Code
- Generator naming convention:</u></strong><strong> </strong>The
- main database code module name would be derived from the actual
- file name, where a database file named <strong>MyData.mdb</strong>
- would result in a code module named <strong>mdbMyData.bas</strong>.
- The table name(s) subsequently drive the subroutine name
- variations, and combined with the field names, create the data
- variable names around which both programs revolve. </font></p>
-
- <p><font size="2" face="Arial">For example, a variable named <strong>mdbMyData.MyTable_FirstName</strong>
- would refer to a field named <strong>FirstName</strong> in a
- table called <strong>MyTable</strong> in a data file named <strong>MyData.mdb</strong>.
- This is both simple and logical and should therefore be easy to
- follow and track.</font></p>
-
- <p><font size="2" face="Arial">The <strong>Urthman's MDB
- Construction Kit</strong>, as an extension of the MDB Code
- Generator, generates import and export code in a module of its
- own. Taking the database file name in the above example, the MDB
- Construction Kitwould create a code module file named <strong>mioMyData.bas</strong>,
- where the '</font><font size="3" face="Arial">io</font><font
- size="2" face="Arial">' stands for </font><font size="3"
- face="Arial">i</font><font size="2" face="Arial">nput and </font><font
- size="3" face="Arial">o</font><font size="2" face="Arial">utput.</font></p>
- <div align="center"><center>
-
- <table border="1" width="90%" bgcolor="#008080">
- <tr>
- <td align="center" width="100%"><a name="Exporting"><font
- color="#FFFF00" face="Arial"><strong>Exporting</strong></font></a><font
- color="#FFFF00" face="Arial"><strong> Data using code
- modules from both programs:</strong></font></td>
- </tr>
- </table>
- </center></div><div align="center"><center>
-
- <table border="0" cellpadding="8" cellspacing="8" width="100%">
- <tr>
- <td bgcolor="#FFFFFF"><font color="#008040" size="2"
- face="Courier New"><strong>' Call for the first record in
- the data table to initialize the database</strong></font><blockquote>
- <p><font color="#000080" size="2" face="Courier New">Call
- mdbMyData.MyTableFirst</font></p>
- </blockquote>
- <p><font color="#008040" size="2" face="Courier New"><strong>'
- Open the ASCII export file</strong></font></p>
- <blockquote>
- <p><font color="#000080" size="2" face="Courier New">Call
- mioMyData.OpenExport("MyTable",
- "C:\Temp\MyDemo.Txt")</font></p>
- </blockquote>
- <p><font color="#008040" size="2" face="Courier New"><strong>'
- Start the process loop</strong></font></p>
- <blockquote>
- <p><font color="#000080" size="2" face="Courier New">Do
- Until mdbMyData.MyTableERR</font></p>
- </blockquote>
- <p><font color="#008040" size="2" face="Courier New"><strong>'
- This line needs the specific criteria for selecting the
- exportable record data<br>
- ' and leaves a record count in the Long numeric DataCount</strong></font></p>
- <blockquote>
- <blockquote>
- <p><font color="#000080" size="2"
- face="Courier New">If ( - what ever criteria - )
- Then DataCount = mioMyData.ExportData</font></p>
- </blockquote>
- </blockquote>
- <p><font color="#008040" size="2" face="Courier New"><strong>'
- Get the next record and continue</strong></font></p>
- <blockquote>
- <blockquote>
- <p><font color="#000080" size="2"
- face="Courier New">Call mdbMyData.MyTableNext</font></p>
- </blockquote>
- <p><font color="#000080" size="2" face="Courier New">Loop</font></p>
- </blockquote>
- <p><font color="#008040" size="2" face="Courier New"><strong>'
- Close the ASCII file and database</strong></font></p>
- <blockquote>
- <p><font color="#000080" size="2" face="Courier New">Call
- mioMyData.CloseFile</font></p>
- </blockquote>
- <blockquote>
- <p><font color="#000080" size="2" face="Courier New">Call
- mdbMyData.CloseMyData</font></p>
- </blockquote>
- </td>
- </tr>
- </table>
- </center></div><div align="center"><center>
-
- <table border="1" width="90%" bgcolor="#008080">
- <tr>
- <td align="center" width="100%"><a name="Importing"><font
- color="#FFFF00" face="Arial"><strong>Importing</strong></font></a><font
- color="#FFFF00" face="Arial"><strong> Data using code
- modules from both programs:</strong></font></td>
- </tr>
- </table>
- </center></div><div align="center"><center>
-
- <table border="0" cellpadding="8" cellspacing="8" width="100%">
- <tr>
- <td bgcolor="#FFFFFF"><font color="#008040" size="2"
- face="Courier New"><strong>' It's not necessary to open
- the database, but the ASCII file needs it.</strong></font><blockquote>
- <p><font color="#000080" size="2" face="Courier New">Call
- mioMyData.OpenImport("MyTable",
- "C:\Temp\Import.Txt")</font></p>
- </blockquote>
- <p><font color="#008040" size="2" face="Courier New"><strong>'
- Start the process loop -- pretty simple?</strong></font></p>
- <blockquote>
- <p><font color="#000080" size="2" face="Courier New">Do
- Until Not mioMyData.ImportData</font></p>
- </blockquote>
- <blockquote>
- <blockquote>
- <p><font color="#000080" size="2"
- face="Courier New">DoEvents</font></p>
- </blockquote>
- <p><font color="#000080" size="2" face="Courier New">Loop</font></p>
- </blockquote>
- <p><font color="#008040" size="2" face="Courier New"><strong>'
- Close the ASCII file and database</strong></font></p>
- <blockquote>
- <p><font color="#000080" size="2" face="Courier New">Call
- mioMyData.CloseFile</font></p>
- </blockquote>
- <blockquote>
- <p><font color="#000080" size="2" face="Courier New">Call
- mdbMyData.CloseMyData</font></p>
- </blockquote>
- </td>
- </tr>
- </table>
- </center></div><div align="center"><center>
-
- <table border="1" width="90%" bgcolor="#008080">
- <tr>
- <td align="center" width="100%"><a name="ExpHeader"><font
- color="#FFFF00" face="Arial"><strong>Exporting Header and
- Trailer Records</strong></font></a></td>
- </tr>
- </table>
- </center></div>
-
- <p><font size="2" face="Arial">Exporting headers using comma
- delimited file formats is a relatively transparent process. If
- the designated output file does not exist, then the first record
- written when the file is opened for export is the header record.
- If the file does already exist, then no header record is
- generated. This header record contains the column headings for
- the data records that follow that header. This allows for quick
- referencing and import into a standard spread sheet for sorting,
- editting and reporting data.</font></p>
-
- <p><font size="2" face="Arial">Fixed length records use headers
- and trailers for supplemental data and control. For this purpose,
- a set of data variables for each header and trailer record is
- made available in the code module, accompanied by a matching
- Export subroutine. Populate the data variables and export the
- header or trailer record.</font></p>
- <div align="center"><center>
-
- <table border="1" width="90%" bgcolor="#008080">
- <tr>
- <td align="center" width="100%"><a name="ImpHeader"><font
- color="#FFFF00" face="Arial"><strong>Importing Header and
- Trailer Records</strong></font></a></td>
- </tr>
- </table>
- </center></div>
-
- <p><font size="2" face="Arial">Importing headers in comma
- delimited formats is used to "realign" column data with
- the table fields. Generally, the first record in a comma
- delimited file is the header record, and if present, these
- heading fields correspond to table field names. Refer to the code
- module comments for more detailed information since some criteria
- depend entirely on the structure of the data file.</font></p>
-
- <p><font size="2" face="Arial">Importing headers and trailers
- with fixed record length files is a slightly more complex
- exercise. The header records generally open with data related to
- the detail records that follow and the common element terminates
- with a trailer record that contains summary information (i.e.,
- record count or revenue total) to serve as a data integrity
- check. A subsequent header resets the common information for
- another batch of detail records.</font></p>
-
- <p><font size="2" face="Arial">The MDB Contruction Kit permits
- the definition and application of header and trailer record
- layouts, including up to three fields whose content can identify
- which header or trailer is being encountered. These recognition
- codes are used in the generated code for (1) recognizing and
- reporting the encounter of any header and trailr record and (2)
- to pass default values into the header and trailer records during
- export.</font></p>
-
- <p><font size="2" face="Arial">A public variable called RecType
- will contain a code identifying the type of record encountered
- during an import pass. The following code sample demonstrates all
- of the import data functions and options.</font></p>
- <div align="center"><center>
-
- <table border="0" cellpadding="8" cellspacing="8" width="100%">
- <tr>
- <td bgcolor="#FFFFFF"><font color="#008040" size="2"
- face="Courier New"><strong>'Open the import file and let
- it rip ...</strong></font><blockquote>
- <p><font color="#000080" size="2" face="Courier New">Call
- mioMyData.OpenImport("MyTable",
- "C:\Temp\Import.Txt")</font></p>
- <p><font color="#000080" size="2" face="Courier New">Do
- Until Not <strong>mioMyData.ImportData</strong></font></p>
- <blockquote>
- <p><font color="#000080" size="2"
- face="Courier New">If <strong>mioMyData.ImportError</strong>
- Then</font></p>
- </blockquote>
- </blockquote>
- <p><font color="#008040" size="2" face="Courier New"><strong>'This
- would occur if and when a comma delimited file header
- record <br>
- 'does not contain enough data elements or primary key
- elements to <br>
- 'provide properly qualified data record content.</strong></font></p>
- <blockquote>
- <blockquote>
- <p><font color="#000080" size="2"
- face="Courier New">End If</font></p>
- </blockquote>
- <blockquote>
- <p><font color="#000080" size="2"
- face="Courier New">Select Case <strong>mioMyData.RecType</strong></font></p>
- <p><font color="#000080" size="2"
- face="Courier New">Case "HDR"</font></p>
- </blockquote>
- </blockquote>
- <p><font color="#008040" size="2" face="Courier New"><strong>'Comma
- Delimited Header record</strong></font></p>
- <blockquote>
- <blockquote>
- <p><font color="#000080" size="2"
- face="Courier New">Case "DTL"</font></p>
- </blockquote>
- </blockquote>
- <p><font color="#008040" size="2" face="Courier New"><strong>'Comma
- Delimited Detail record - save the data using the other<br>
- 'code module generated by the Urthman's MDB Code
- Generator.</strong></font></p>
- <blockquote>
- <blockquote>
- <blockquote>
- <p><font color="#000080" size="2"
- face="Courier New">Call mdbMyData.MyTableSave</font></p>
- </blockquote>
- </blockquote>
- </blockquote>
- <blockquote>
- <blockquote>
- <p><font color="#000080" size="2"
- face="Courier New">Case "FHD"</font></p>
- </blockquote>
- </blockquote>
- <p><font color="#008040" size="2" face="Courier New"><strong>'Fixed
- Length File Header record - the beginning of a file</strong></font></p>
- <blockquote>
- <blockquote>
- <p><font color="#000080" size="2"
- face="Courier New">Case "FTL"</font></p>
- </blockquote>
- </blockquote>
- <p><font color="#008040" size="2" face="Courier New"><strong>'Fixed
- Length File Trailer record - the end or summary of a file</strong></font></p>
- <blockquote>
- <blockquote>
- <p><font color="#000080" size="2"
- face="Courier New">Case "BHD"</font></p>
- </blockquote>
- </blockquote>
- <p><font color="#008040" size="2" face="Courier New"><strong>'Fixed
- Length Batch Header record - the beginning of a batch</strong></font></p>
- <blockquote>
- <blockquote>
- <p><font color="#000080" size="2"
- face="Courier New">Case "BTL"</font></p>
- </blockquote>
- </blockquote>
- <p><font color="#008040" size="2" face="Courier New"><strong>'Fixed
- Length Batch Trailer record - the end or summary of a
- batch</strong></font></p>
- <blockquote>
- <blockquote>
- <p><font color="#000080" size="2"
- face="Courier New">Case "DET"</font></p>
- </blockquote>
- </blockquote>
- <p><font color="#008040" size="2" face="Courier New"><strong>'Fixed
- Length Detail record - save the data using the other<br>
- 'code module generated by the Urthman's MDB Code
- Generator.</strong></font></p>
- <blockquote>
- <blockquote>
- <blockquote>
- <p><font color="#000080" size="2"
- face="Courier New">Call mdbMyData.MyTableSave</font></p>
- </blockquote>
- </blockquote>
- </blockquote>
- <blockquote>
- <blockquote>
- <p><font color="#000080" size="2"
- face="Courier New">End Select</font></p>
- </blockquote>
- <p><font color="#000080" size="2" face="Courier New">Loop</font></p>
- </blockquote>
- <p><font color="#008040" size="2" face="Courier New"><strong>'
- Close the ASCII file and database</strong></font></p>
- <blockquote>
- <p><font color="#000080" size="2" face="Courier New">Call
- mioMyData.CloseFile</font></p>
- </blockquote>
- <blockquote>
- <p><font color="#000080" size="2" face="Courier New">Call
- mdbMyData.CloseMyData</font></p>
- </blockquote>
- </td>
- </tr>
- </table>
- </center></div><div align="center"><center>
-
- <table border="1" width="90%" bgcolor="#008080">
- <tr>
- <td align="center" width="100%"><a name="ImpMods"><font
- color="#FFFF00" face="Arial"><strong>Modifying the Import
- Routines.</strong></font></a></td>
- </tr>
- </table>
- </center></div>
-
- <p><font size="2" face="Arial">The import routines are heavily
- commented, including indicating where certain operations can take
- place to prevent unwanted data overwrites. This is primarily a
- function of comma delimited file exchanges. The strategy involves
- exporting data to a comma delimited file and importing this data
- into a spread sheet for review, analysis and edits. After making
- these adjustments, some columns may have been moved around for
- the convenience of the user, and some insignificant columns may
- have been deleted.</font></p>
-
- <p><font size="2" face="Arial">During the standard import sweep,
- the column headings allow for proper realignment with the
- original corresponding table fields, however, the deleted columns
- would result in null data being written into that field. In order
- to preserve the previously populated data of those deleted
- columns, a record-find operation can be called. This serves two
- purposes: (1) to populate the deleted column fields with
- "Default" data and (2) to clear or neutralize ALL data
- fields prior to generating a new data record.</font></p>
-
- <p><font size="2" face="Arial">The optimal location and method
- for this function are fully commented in the specific import
- routine. This code makes the assumption that the Urthman's MDB
- Code Generator has been or will be used for creating the general
- code module for all other database functions.</font></p>
-
- <hr>
- <div align="center"><center>
-
- <table border="0" cellpadding="8" cellspacing="0" width="100%">
- <tr>
- <td align="center" width="33%"><div align="center"><center><table
- border="0" cellpadding="8" cellspacing="0"
- bgcolor="#C0C0C0">
- <tr>
- <td align="right" valign="top"><font
- color="#000000" size="1" face="Arial"><strong>Contact:</strong></font></td>
- <td valign="top"><font color="#000000" size="1"
- face="Arial"><strong>John Stanley Enterprises</strong><br>
- PO Box 1672<br>
- Valrico, FL 33595-1672</font></td>
- </tr>
- <tr>
- <td align="right" valign="top"><font size="1"
- face="Arial"><strong>URL:</strong></font></td>
- <td><a href="http://www.a-znet.com/jse/"><font
- size="1" face="Arial"><strong>http://www.a-znet.com/jse/</strong></font></a></td>
- </tr>
- <tr>
- <td align="right" valign="top"><font
- color="#000000" size="1" face="Arial"><strong>Email:</strong></font></td>
- <td valign="top"><a href="mailto:urthman@usa.net"><font
- color="#000000" size="1" face="Arial"><strong>urthman@usa.net</strong></font></a><font
- color="#000000" size="1" face="Arial"><strong> </strong></font></td>
- </tr>
- </table>
- </center></div></td>
- <td align="center" width="33%"> </td>
- <td align="center" width="33%"><font size="1"
- face="Arial">Microsoft, Windows, NT and Visual Basic are
- registered trademarks of the Microsoft Corporation.</font></td>
- </tr>
- </table>
- </center></div>
- </body>
- </html>
-